﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class PhongDAO
    {
        public class SqlConnectionData
        {
            public static SqlConnection Connection()
            {
                SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=QLKSAN;Integrated Security=True");
                return conn;
            }
        }
        public static DataTable GetAll()
        {
            SqlConnection conn = SqlConnectionData.Connection();
            SqlCommand cmd = new SqlCommand("sp_PhongGetAll", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public static DataTable GetLoaiPhongforCombobox()
        {
            SqlConnection conn = SqlConnectionData.Connection();
            SqlCommand cmd = new SqlCommand("sp_GetLoaiPhongforCombobox", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;

        }
        public PhongDTO GetAllWithMaPhong(int maPhong)
        {
            PhongDTO data = new PhongDTO();
            SqlConnection conn = SqlConnectionData.Connection();
            SqlCommand cmd = new SqlCommand("sp_PhongGetAllWithMaPhong", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@maPhong", SqlDbType.Int);
            cmd.Parameters["@maPhong"].Value = maPhong;
            conn.Open();
            SqlDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                data.MaPhong = Convert.ToInt32(dr["MaPhong"]);
                data.TenPhong = Convert.ToString(dr["TenPhong"]);
                data.MaLoaiPhong = Convert.ToInt32(dr["MaLoaiPhong"]);
                data.GhiChu = Convert.ToString(dr["GhiChu"]);
                data.TinhTrangPhong = Convert.ToInt32(dr["TinhTrangPhong"]);
            }
            return data;
        }
        public static void UpdateTinhTrangPhong(int maphong,int tinhtrang)
        {
            SqlConnection conn = SqlConnectionData.Connection();
            SqlCommand cmd = new SqlCommand("sp_UpdateTinhTrangPhong", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@MaPhong", SqlDbType.Int);
            cmd.Parameters.Add("@TinhTrangPhong", SqlDbType.Int);
            cmd.Parameters["@MaPhong"].Value = maphong;
            cmd.Parameters["@TinhTrangPhong"].Value = tinhtrang;
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public static DataTable PhongTheoMaLoaiPhong(int maloaiphong)
        {
            string sql = string.Format("Select * From PHONG where MaLoaiPhong= " + maloaiphong);
            DataTable dt = DataProvider.ExecuteQuery(sql);
            return dt;
        }
         public static DataTable PhongTheoTen(string tenphong)
        {
            SqlConnection conn = SqlConnectionData.Connection();
            SqlCommand cmd = new SqlCommand("sp_PhongTheoTen", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@TenPhong", SqlDbType.NVarChar,50);
            cmd.Parameters["@TenPhong"].Value = tenphong;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
         public static DataTable PhongTheoLoai(string loai)
         {
             string sql = string.Format("Select * From LOAIPHONG l, PHONG p Where l.MaLoaiPhong=p.MaLoaiPhong and TenLoaiPhong like '" + loai + "' and TinhtrangPhong=0");
             DataTable dt = DataProvider.ExecuteQuery(sql);
             return dt;
         }
        
         public static void InsertPhong(PhongDTO dto)
         {
             SqlConnection conn = SqlConnectionData.Connection();
             SqlCommand cmd = new SqlCommand("sp_InsertPhong", conn);
             cmd.CommandType = CommandType.StoredProcedure;
             cmd.Parameters.Add("@TenPhong", SqlDbType.NVarChar, 50);
             cmd.Parameters.Add("@MaLoaiPhong", SqlDbType.Int);
             cmd.Parameters.Add("@TinhTrangPhong", SqlDbType.Int);
             cmd.Parameters.Add("@GhiChu", SqlDbType.NVarChar, 500);
             cmd.Parameters["@TenPhong"].Value = dto.TenPhong;
             cmd.Parameters["@MaLoaiPhong"].Value = dto.MaLoaiPhong;
             cmd.Parameters["@TinhTrangPhong"].Value = dto.TinhTrangPhong;
             cmd.Parameters["@GhiChu"].Value = dto.GhiChu;
             conn.Open();
             cmd.ExecuteNonQuery();
             conn.Close();
         }
         public static void UpdatePhong(PhongDTO dto)
         {
             SqlConnection conn = SqlConnectionData.Connection();
             SqlCommand cmd = new SqlCommand("sp_UpdatePhong", conn);
             cmd.CommandType = CommandType.StoredProcedure;
             cmd.Parameters.Add("@MaPhong", SqlDbType.Int);
             cmd.Parameters.Add("@TenPhong", SqlDbType.NVarChar, 50);
             cmd.Parameters.Add("@MaLoaiPhong", SqlDbType.Int);
             cmd.Parameters.Add("@TinhTrangPhong", SqlDbType.Int);
             cmd.Parameters.Add("@GhiChu", SqlDbType.NVarChar, 500);
             cmd.Parameters["@MaPhong"].Value = dto.MaPhong;
             cmd.Parameters["@TenPhong"].Value = dto.TenPhong;
             cmd.Parameters["@MaLoaiPhong"].Value = dto.MaLoaiPhong;
             cmd.Parameters["@TinhTrangPhong"].Value = dto.TinhTrangPhong;
             cmd.Parameters["@GhiChu"].Value = dto.GhiChu;
             conn.Open();
             cmd.ExecuteNonQuery();
             conn.Close();
         }
         public static void DeletePhong(PhongDTO dto)
         {
             SqlConnection conn = SqlConnectionData.Connection();
             SqlCommand cmd = new SqlCommand("sp_DeletePhong", conn);
             cmd.CommandType = CommandType.StoredProcedure;
             cmd.Parameters.Add("@MaPhong", SqlDbType.Int);
             cmd.Parameters["@MaPhong"].Value = dto.MaPhong;
             conn.Open();
             cmd.ExecuteNonQuery();
             conn.Close();
         }
         //************************************************//
         //Author:Nguyen Tan Phat
         //MSSV:1173025
         //Class:11HT
         #region
         public static DataSet SelectAll()
         {
             return SqlHelper.ExecuteDataset(ConnectionString.Text, CommandType.StoredProcedure, "PHONG_SelectAll", null);
         }
         public static DataSet SelectCombobox(int MaLoaiPhong)
         {
             var pr = new SqlParameter[1];
             pr[0] = new SqlParameter(@"MaLoaiPhong", MaLoaiPhong);
             return SqlHelper.ExecuteDataset(ConnectionString.Text, CommandType.StoredProcedure, "PHONG_SelectCombobox", pr);
         }
         public static DataSet Search(PhongDTO phong,int DonGiaTren,int DonGiaDuoi)
         {
             var pr = new SqlParameter[5];
             pr[0] = new SqlParameter(@"MaPhong", phong.MaPhong);
             pr[1] = new SqlParameter(@"MaLoaiPhong", phong.MaLoaiPhong);             
             pr[2] = new SqlParameter(@"TinhTrangPhong", phong.TinhTrangPhong);
             pr[3] = new SqlParameter(@"DonGiaDuoi", DonGiaDuoi);
             pr[4] = new SqlParameter(@"DonGiaTren", DonGiaTren);
             return SqlHelper.ExecuteDataset(ConnectionString.Text, CommandType.StoredProcedure, "PHONG_Search", pr);
         }
         public static DataSet SelectWithTinhTrang(int tinhTrang)
         {
             var pr = new SqlParameter[1];
             pr[0] = new SqlParameter(@"TinhTrang", tinhTrang);
             return SqlHelper.ExecuteDataset(ConnectionString.Text, CommandType.StoredProcedure, "PHONG_SelectWithTinhTrang", pr);
         }
         public static void Update(PhongDTO phong)
         {
             var pr = new SqlParameter[5];
             pr[0] = new SqlParameter(@"MaPhong", phong.MaPhong);
             pr[1] = new SqlParameter(@"TenPhong", phong.TenPhong);
             pr[2] = new SqlParameter(@"MaLoaiPhong", phong.MaLoaiPhong);
             pr[3] = new SqlParameter(@"TinhTrangPhong", phong.TinhTrangPhong);
             pr[4] = new SqlParameter(@"GhiChu", phong.GhiChu);
             SqlHelper.ExecuteNonQuery(ConnectionString.Text, CommandType.StoredProcedure, "PHONG_Update", pr);
         }
         #endregion
        //************************************************//
    }
}
